/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package accessor;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Vector;
import model.Model;

/**
 *
 * @author QUYET
 */
public class OrderAccessor extends Model {

    public int addOrder(int customerID, String orderDate, int orderStatusID, String deliveryDate) {
        int rowAffected = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Insert into tblOrders(customerID,orderDate,orderStatus,deliveryDate) values(?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, customerID);
            ps.setString(2, orderDate);
            ps.setInt(3, orderStatusID);
            ps.setString(4, deliveryDate);
            rowAffected = ps.executeUpdate();
            closeObject(ps);
            closeObject(conn);
            return rowAffected;
        } catch (Exception e) {
            e.printStackTrace();
            return rowAffected;
        }
    }

    public int addOrderDetails(int orderID, int productID, int quantity, Double price, Double totalPrice) {
        int rowAffected = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Insert into tblOrderDetails(orderID,productID,quantity,price,totalPrice) values(?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, orderID);
            ps.setInt(2, productID);
            ps.setInt(3, quantity);
            ps.setDouble(4, price);
            ps.setDouble(5, totalPrice);
            rowAffected = ps.executeUpdate();
            closeObject(ps);
            closeObject(conn);
            return rowAffected;
        } catch (Exception e) {
            e.printStackTrace();
            return rowAffected;
        }
    }

    public int getInsertID() {
        int id = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement("Select TOP 1 * from tblOrders order by orderID DESC");
            rs = ps.executeQuery();
            if (rs.next()) {
                id = rs.getInt("orderID");
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return id;
        } catch (Exception ex) {
            ex.printStackTrace();
            return 0;
        }
    }

    public List<Order> getAllOrders(int statusID) {
        List<Order> orderList = new ArrayList<Order>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "SELECT tblOrders.orderID,tblOrders.customerID,tblOrders.orderDate,tblOrders.orderStatus,"
                    + "tblOrders.deliveryDate,tblOrders.actualShippingDate,tblCustomerProfile.firstName,tblCustomerProfile.lastName,tblOrderStatus.statusName "
                    + "FROM tblOrders INNER JOIN tblCustomer ON tblOrders.customerID = tblCustomer.customerID INNER JOIN "
                    + "tblCustomerProfile ON tblCustomer.customerID = tblCustomerProfile.customerID "
                    + "INNER JOIN tblOrderStatus ON tblOrders.orderStatus = tblOrderStatus.orderStatusID";
            if (statusID > 0) {
                sql += " Where tblOrders.orderStatus = ?";
            }
            ps = conn.prepareStatement(sql);
            if (statusID > 0) {
                ps.setInt(1, statusID);
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                Order order = new Order();
                order.setOrderID(rs.getInt("orderID"));
                order.setOrderDate(rs.getString("orderDate"));
                order.setStatusID(rs.getInt("orderStatus"));
                order.setDeliveryDate(rs.getString("deliveryDate"));
                order.setActualShippingDate(rs.getString("actualShippingDate"));
                order.setStatusName(rs.getString("statusName"));
                Customer customer = new Customer();
                customer.setCustomerID(rs.getInt("customerID"));
                customer.setFirstName(rs.getString("firstName"));
                customer.setLastName(rs.getString("lastName"));
                order.setCustomer(customer);
                orderList.add(order);
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return orderList;
        } catch (Exception ex) {
            ex.printStackTrace();
            return new ArrayList<Order>();
        }
    }

    public List<OrderDetail> getProductsByOrder(int orderID) {
        List<OrderDetail> orderDetailtList = new ArrayList<OrderDetail>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "SELECT tblOrderDetails.orderDetailsID,tblOrderDetails.productID,tblOrderDetails.quantity,tblOrderDetails.price,tblOrderDetails.totalPrice, "
                    + "tblProducts.productName, tblProducts.quantity AS quantityInStock, tblProducts.defaultImage "
                    + "FROM tblProducts INNER JOIN tblOrderDetails ON tblProducts.productID = tblOrderDetails.productID "
                    + "Where tblOrderDetails.orderID = ?";

            ps = conn.prepareStatement(sql);
            ps.setInt(1, orderID);
            rs = ps.executeQuery();
            while (rs.next()) {
                OrderDetail detail = new OrderDetail();
                detail.setOrderDetailID(rs.getInt("orderDetailsID"));
                detail.setQuantity(rs.getInt("quantity"));
                detail.setPrice(rs.getDouble("price"));
                detail.setTotalPrice(rs.getDouble("totalPrice"));
                Products p = new Products();
                p.setProductID(rs.getInt("productID"));
                p.setProductName(rs.getString("productName"));
                p.setQuantity(rs.getInt("quantityInStock"));
                p.setDefaultImage(rs.getString("defaultImage"));
                detail.setProducts(p);
                orderDetailtList.add(detail);
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return orderDetailtList;
        } catch (Exception ex) {
            ex.printStackTrace();
            return new ArrayList<OrderDetail>();
        }
    }

    public Order getOrderByID(int orderID) {
        Order order = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "SELECT tblOrders.customerID,tblOrders.deliveryDate,tblOrders.actualShippingDate,tblOrders.orderDate,tblOrders.orderStatus, "
                    + " tblCustomer.email,tblCustomer.creditPoint,tblCustomerProfile.firstName,tblCustomerProfile.lastName,tblOrderStatus.statusName "
                    + "FROM tblOrders INNER JOIN tblOrderStatus ON tblOrders.orderStatus = tblOrderStatus.orderStatusID INNER JOIN "
                    + "tblCustomer ON tblOrders.customerID = tblCustomer.customerID INNER JOIN tblCustomerProfile ON tblCustomer.customerID = tblCustomerProfile.customerID";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                order = new Order();
                order.setOrderID(orderID);
                order.setOrderDate(rs.getString("orderDate"));
                order.setStatusID(rs.getInt("orderStatus"));
                order.setDeliveryDate(rs.getString("deliveryDate"));
                order.setActualShippingDate(rs.getString("actualShippingDate"));
                order.setStatusName(rs.getString("statusName"));
                Customer customer = new Customer();
                customer.setCustomerID(rs.getInt("customerID"));
                customer.setEmail(rs.getString("email"));
                customer.setCreaditPoint(rs.getInt("creditPoint"));
                customer.setFirstName(rs.getString("firstName"));
                customer.setLastName(rs.getString("lastName"));
                order.setCustomer(customer);
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return order;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }

    public Double getTotalPaidByOrder(int orderID) {
        Double total = 0.0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement("SELECT SUM(totalPrice) as 'totalPrice' FROM tblOrderDetails Where tblOrderDetails.orderID = ?");
            ps.setInt(1, orderID);
            rs = ps.executeQuery();
            if (rs.next()) {
                total = rs.getDouble("totalPrice");
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return total;
        } catch (Exception ex) {
            ex.printStackTrace();
            return 0.0;
        }
    }

    public Double getTotalPaidHistory(int customerID) {
        Double total = 0.0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement("SELECT SUM(totalPrice) as 'totalPrice' FROM tblOrders INNER JOIN tblOrderDetails ON tblOrders.orderID = tblOrderDetails.orderID Where tblOrders.customerID = ? And tblOrders.orderStatus IN(3,5) ");
            ps.setInt(1, customerID);
            rs = ps.executeQuery();
            if (rs.next()) {
                total = rs.getDouble("totalPrice");
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return total;
        } catch (Exception ex) {
            ex.printStackTrace();
            return 0.0;
        }
    }

    public List<Vector> getAllOrderStatus() {
        List<Vector> statusList = new ArrayList<Vector>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement("Select * from tblOrderStatus");
            rs = ps.executeQuery();
            while (rs.next()) {
                Vector vector = new Vector();
                vector.add(rs.getInt("orderStatusID"));
                vector.add(rs.getString("statusName"));
                statusList.add(vector);
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return statusList;
        } catch (Exception ex) {
            ex.printStackTrace();
            return statusList;
        }

    }

    public List<OrderDetail> getOrderDetails(int orderID) {
        List<OrderDetail> detailList = new ArrayList<OrderDetail>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "SELECT tblOrderDetails.orderDetailsID,tblOrderDetails.productID,tblOrderDetails.quantity,"
                    + "tblOrderDetails.price,tblOrderDetails.totalPrice,tblProducts.productName,tblProducts.defaultImage "
                    + " FROM tblOrderDetails INNER JOIN tblProducts ON tblOrderDetails.productID = tblProducts.productID Where orderID = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, orderID);
            rs = ps.executeQuery();
            while (rs.next()) {
                OrderDetail orderDetail = new OrderDetail();
                orderDetail.setOrderDetailID(rs.getInt("orderDetailsID"));
                orderDetail.setQuantity(rs.getInt("quantity"));
                orderDetail.setPrice(rs.getDouble("price"));
                orderDetail.setTotalPrice(rs.getDouble("totalPrice"));
                Products p = new Products();
                p.setProductID(rs.getInt("productID"));
                p.setProductName(rs.getString("productName"));
                p.setDefaultImage(rs.getString("defaultImage"));
                orderDetail.setProducts(p);
                detailList.add(orderDetail);
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return detailList;
        } catch (Exception ex) {
            ex.printStackTrace();
            return detailList;
        }
    }

    public int getStatus(int orderID) {
        int id = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement("Select * from tblOrders Where  orderID = ?");
            ps.setInt(1, orderID);
            rs = ps.executeQuery();
            if (rs.next()) {
                id = rs.getInt("orderStatus");
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return id;
        } catch (Exception ex) {
            ex.printStackTrace();
            return 0;
        }
    }

    public int updateStatus(int orderID, int statusID) {
        int affRow = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Update tblOrders set orderStatus = ? Where orderID = ?";
            if(statusID==5){
                sql = "Update tblOrders set orderStatus = ?,actualShippingDate = ? Where orderID = ?";
            }
            ps = conn.prepareStatement(sql);
            ps.setInt(1, statusID);
            if(statusID==5){
                ps.setString(2,new SimpleDateFormat("MM/dd/yyyy H:m:s").format(new Date()));
                ps.setInt(3, orderID);
            } else{
                ps.setInt(2, orderID);
            }
            
            affRow = ps.executeUpdate();
            closeObject(ps);
            closeObject(conn);
            return affRow;
        } catch (Exception ex) {
            ex.printStackTrace();
            return 0;
        }
    }
}
